package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

/**
 * 旅游路线SQL语句封装类
 */
@Component
public class TravelRouteSqlProvider {

    //所有的未删除的旅游路线
    public String count(){ return "SELECT count(*) FROM t_travel_route WHERE DELETE_STATUS=0"; }

    //已发布的所有旅游路线
    public String count2(){ return "SELECT count(*) FROM t_travel_route WHERE DELETE_STATUS=0 AND STATE=1"; }

    //通过ID查询未删除的旅游路线
    public String findById() {
        return "SELECT * FROM t_travel_route WHERE ID = #{id} AND DELETE_STATUS=0";
    }

    //所有的旅游路线通过ID进行排序
    public String findList(){
        return "SELECT * FROM t_travel_route WHERE DELETE_STATUS=0 ORDER BY ID ASC";
    }

    //发布的旅游路线通过ID进行排序
    public String indexList(){
        return "SELECT * FROM t_travel_route WHERE DELETE_STATUS=0 AND STATE=1 ORDER BY ID ASC";
    }

    //通过标题模糊查询并且通过ID排序
    public String findListByQuery(){
        return "SELECT * FROM t_travel_route WHERE DELETE_STATUS=0 AND TITLE LIKE #{query,jdbcType=VARCHAR} ORDER BY ID ASC";
    }

    //修改旅游路线
    public String update(){
        return "UPDATE t_travel_route SET TITLE=#{title},START_SITE=#{startSite},END_SITE=#{endSite},END_TIME=#{endTime},START_TIME=#{startTime},DAY=#{day}," +
                "PRODUCT_CODE=#{productCode},PRICE=#{price},STATE=#{state},IMG_URL=#{imgUrl},INTRO=#{intro} WHERE ID=#{id}";
    }

    //保存旅游路线
    public String save(){
        return "INSERT INTO t_travel_route(ADD_TIME,TITLE,START_SITE,END_SITE,END_TIME," +
                "START_TIME,DAY,PRODUCT_CODE,PRICE,STATE,IMG_URL,INTRO) VALUES(NOW(),#{title},#{startSite}," +
                "#{endSite},#{endTime},#{startTime},#{day},#{productCode},#{price},#{state},#{imgUrl},#{intro})";
    }

    //通过ID删除旅游路线
    public String deleteById(){
        return "UPDATE t_travel_route SET DELETE_STATUS=1 WHERE ID=#{id}";
    }

    //修改旅游路线状态  0待发布 1已发布 2撤销
    public String state0count(){ return "SELECT count(*) FROM t_travel_route WHERE DELETE_STATUS=0 AND STATE=0"; }
    public String state1count(){ return "SELECT count(*) FROM t_travel_route WHERE DELETE_STATUS=0 AND STATE=1"; }
    public String state2count(){ return "SELECT count(*) FROM t_cms_travel_route WHERE DELETE_STATUS=0 AND STATE=2"; }

}
